﻿use master

use master
 
select * from AccountInfo  --个人信息
select * from BankCard  --银行卡信息
select * from CardStateChange  --银行卡状态更改表
select * from CardTransfer  --转账表
select * from CardExchange  --交易记录
drop function func_SUMmoney
--（1）编写一个函数求该银行的金额总和
	create function func_SUMmoney()
	returns money
	as
	begin
	declare @sum money
	select  @sum= sum(CardMoney)from BankCard
	return @sum
	end
	select dbo.func_SUMmoney()
 
--（2）传入账户编号，返回账户真实姓名
    create function func_SUMname(@id int )
	returns varchar(20)
	as
	begin
	declare @name varchar(20)
	select @name= RealName from AccountInfo  where AccountId = @id
	return @name
	end

	select dbo.func_SUMname(1)
--（3）传递开始时间和结束时间，返回交易记录（存钱取钱），交易记录中包含 真实姓名，卡号，存钱金额，取钱金额，交易时间。
	drop function GetRecordByTime

	create function GetRecordByTime(@start smalldatetime,@end smalldatetime)
	returns @result table
	(
		 RealName varchar(30),
		 CardNo varchar(50),
		 MoneyInBank money,
		 MoneyOutBank money,
		 ExchangeTime smalldatetime
	)
	as
	begin
		insert into @result
		select RealName 姓名,CardExchange.CardNo 卡号,MoneyInBank 存钱金额,MoneyOutBank 取钱金额,ExchangeTime 交易时间
        from CardExchange 
        join BankCard on BankCard.CardNo=CardExchange.CardNo 
		join AccountInfo on AccountInfo.AccountId=BankCard.AccountId
		where AccountInfo.OpenTime  = @start and  CardExchange.ExchangeTime = @end

		--where ExchangeTime between @start+'00:00:00' and @end+'23:59:59'
		return
	end

select *from GetRecordByTime('2022-10-07 16:17:00','2022-10-07 16:17:00')
 
 


--方案一（逻辑复杂，函数内容除了返回结果的sql语句还有其他内容，例如定义变量等）：

--（4）查询银行卡信息，将银行卡状态1,2,3,4分别转换为汉字“正常,挂失,冻结,注销”，根据银行卡余额显示银行卡等级 30万以下为“普通用户”,30万及以上为"VIP用户"，分别显示卡号，身份证，姓名，余额，用户等级，银行卡状态。

--方案一：直接在sql语句中使用case when 
    select BankCard.CardNo, AccountInfo.RealName ,AccountInfo.AccountCode , BankCard.CardMoney ,
	(
	   case
	    when CardState = 1 then  '正常' 
	    when CardState = 2 then  '挂失' 
		when CardState = 2 then  '冻结' 
		else  '注销' 
		end
	 )  as  状态,
	(
	 case when(BankCard.CardMoney<300000) then '普通用户'
	  else  'VIP用户'
	  end
	  ) as 银行卡等级
	from  AccountInfo  inner join  BankCard on AccountInfo.AccountId = BankCard.AccountId

--方案二：将等级和状态用函数实现
	go
	create function fun_GetBankInfoByCardNo(@CardNo varchar(30))
	returns @BankInfo table(
		CardNo varchar(30),
		AccountCode varchar(30),
		Name varchar(30),
		CardMoney money,
		UserLevel varchar(30),
		BankCardStatus varchar(30)
	)
	as
	begin
		insert into @BankInfo
		select B.CardNo,AccountCode,RealName,CardMoney,
		(
			case when CardMoney<300000 then '普通用户'
			else 'VIP用户'
			end
		)UserLevel,
		(
			case when CardState=1 then '正常'
				 when CardState=2 then '挂失'
				 when CardState=3 then '冻结' else '注销'
			end
		)BankCardStatus
		from BankCard B
		join AccountInfo A on A.AccountId = B.AccountId
		where CardNo = @CardNo
		return
	end
	go

	select * from fun_GetBankInfoByCardNo('6225547858741263')

	select * from AccountInfo
	select * from CardExchange
	select * from BankCard

--（5）编写函数，根据出生日期求年龄，年龄求实岁，例如：

​		--生日为2000-5-5，当前为2018-5-4，年龄为17岁
​		--生日为2000-5-5，当前为2018-5-6，年龄为18岁

create table Emp  
(
	EmpId int primary key identity(1,2), --自动编号
	empName varchar(20), --姓名
	empSex varchar(4),   --性别
	empBirth smalldatetime --生日
)
insert into Emp(empName,empSex,empBirth) values('刘备','男','2008-5-8')
insert into Emp(empName,empSex,empBirth) values('关羽','男','1998-10-10')
insert into Emp(empName,empSex,empBirth) values('张飞','男','1999-7-5')
insert into Emp(empName,empSex,empBirth) values('赵云','男','2003-12-12')
insert into Emp(empName,empSex,empBirth) values('马超','男','2003-1-5')
insert into Emp(empName,empSex,empBirth) values('黄忠','男','1988-8-4')
insert into Emp(empName,empSex,empBirth) values('魏延','男','1998-5-2')
insert into Emp(empName,empSex,empBirth) values('简雍','男','1992-2-20')
insert into Emp(empName,empSex,empBirth) values('诸葛亮','男','1993-3-1')
insert into Emp(empName,empSex,empBirth) values('徐庶','男','1994-8-5')

--看别人做的
select * from Emp
go
create function fun_GetAge(@id int,@NowTime datetime)
returns int
as
begin
	declare @BirthTime datetime
	declare @age int
	select @BirthTime = (select empBirth from Emp where EmpId = @id)
	select @age = DATEDIFF(YY,@BirthTime,@NowTime)
	select @age = case when DATEDIFF(DD,DATEADD(YY,@age,@BirthTime),@NowTime)<0 then @age-1 else @age end
	return @age
end
go

select dbo.fun_GetAgeByBirth(9,GETDATE()) as 年龄